package com.hulu.yec.sys.yecclass.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hulu.yec.sys.yecclass.entity.ClassEntity;

public class ClassDao {

	/**
	 * 班级信息查询
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public static List<ClassEntity> list(String sql) throws SQLException {

		List<ClassEntity> listUser = new ArrayList<>();

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/exam", "root", "root");
			preparedStatement = connection.prepareStatement(sql);

			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				ClassEntity listEntity = new ClassEntity();

				listEntity.setId(resultSet.getInt("id"));
				listEntity.setName(resultSet.getString("name"));
				listEntity.setCourse_name(resultSet.getString("course_name"));
				listEntity.setOperateTime(resultSet
						.getTimestamp("operate_time"));
				listEntity.setState(resultSet.getShort("state"));
				listEntity.setMemo(resultSet.getString("memo"));
				listUser.add(listEntity);
			}
			return listUser;
		} catch (ClassNotFoundException | SQLException e) {

			e.printStackTrace();
			throw new RuntimeException("失败~");
		} finally {
			// 4、资源关闭

			if (resultSet != null) {
				resultSet.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}

		}
	}

	/**
	 * Class新增和修改的课程回显
	 * 
	 * @param sqll
	 * @return
	 * @throws SQLException
	 */

	public static List<ClassEntity> listl(String sqll) throws SQLException {

		List<ClassEntity> listUser = new ArrayList<>();

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/exam", "root", "root");
			preparedStatement = connection.prepareStatement(sqll);

			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				ClassEntity listEntity = new ClassEntity();
				listEntity.setId(resultSet.getInt("id"));
				listEntity.setCourse_name(resultSet.getString("course_name"));
				listUser.add(listEntity);
			}
			return listUser;
		} catch (ClassNotFoundException | SQLException e) {

			e.printStackTrace();
			throw new RuntimeException("失败~");
		} finally {
			// 4、资源关闭

			if (resultSet != null) {
				resultSet.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}

		}
	}

	/**
	 * 状态的修改
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public static int states(String sql) throws SQLException {

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/exam", "root", "root");

			preparedStatement = connection.prepareStatement(sql);
			return preparedStatement.executeUpdate(sql);

		} catch (ClassNotFoundException | SQLException e) {
			throw new RuntimeException("简历查询失败~");
		} finally {
			// 4、资源关闭

			if (resultSet != null) {
				resultSet.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}

		}

	}

	/**
	 * 班级管理修改的班级信息回信
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public static List<ClassEntity> lists(String sql) throws SQLException {

		List<ClassEntity> listUser = new ArrayList<>();

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/exam", "root", "root");
			preparedStatement = connection.prepareStatement(sql);

			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				ClassEntity listEntity = new ClassEntity();

				listEntity.setId(resultSet.getInt("id"));
				listEntity.setName(resultSet.getString("name"));
				listEntity.setOperateTime(resultSet
						.getTimestamp("operate_time"));
				listEntity.setState(resultSet.getShort("state"));
				listEntity.setMemo(resultSet.getString("memo"));
				listUser.add(listEntity);
			}
			return listUser;
		} catch (ClassNotFoundException | SQLException e) {

			e.printStackTrace();
			throw new RuntimeException("失败~");
		} finally {
			// 4、资源关闭

			if (resultSet != null) {
				resultSet.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}

		}

	}

}
